USE WTPRAC;
CREATE TABLE PERSON (
	id int NOT NULL AUTO_INCREMENT,
	first_name varchar(20) NOT NULL,
	last_name varchar(20) NOT NULL,
	middle_name varchar(15) default "",
	phone_number varchar(15) default "",
	email varchar(40) NOT NULL UNIQUE,
	birth date NOT NULL,
	CONSTRAINT PK_PERSON PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

CREATE TABLE STOP (
	id int NOT NULL AUTO_INCREMENT,
	stop_name varchar(20) NOT NULL,
	CONSTRAINT PK_STOP PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

CREATE TABLE ROUTE (
	id int NOT NULL AUTO_INCREMENT,
	number int NOT NULL,
	route_name varchar(30) NOT NULL,
	active bit NOT NULL,
	CONSTRAINT PK_ROUTE PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

CREATE TABLE ROUTE_STOPS (
	id int NOT NULL AUTO_INCREMENT,
	stop_number int NOT NULL,
	route_id int NOT NULL,
	stop_id int NOT NULL,
	CONSTRAINT U_ROUTE_STOPS UNIQUE (route_id, stop_number),
	CONSTRAINT U_ROUTE_STOPS_1 UNIQUE (route_id, stop_id),
    CONSTRAINT PK_ROUTE_STOPS PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

ALTER TABLE ROUTE_STOPS
	ADD CONSTRAINT FK_ROUTE_STOPS_ROUTE
		FOREIGN KEY (route_id) REFERENCES ROUTE(id)
			ON DELETE CASCADE;

ALTER TABLE ROUTE_STOPS
	ADD CONSTRAINT FK_ROUTE_STOPS_STOP
		FOREIGN KEY (stop_id) REFERENCES STOP(id)
			ON DELETE CASCADE;

CREATE TABLE COMPANY (
	id tinyint NOT NULL AUTO_INCREMENT,
	company_name varchar(30) NOT NULL,
	CONSTRAINT PK_COMPANY PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

CREATE TABLE TRIP (
	id int NOT NULL AUTO_INCREMENT,
	number int NOT NULL,
	route_id int NOT NULL,
	company_id tinyint NOT NULL,
	seats tinyint NOT NULL,
	CONSTRAINT PK_TRIP PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

ALTER TABLE TRIP	
	ADD CONSTRAINT FK_TRIP_ROUTE 
		FOREIGN KEY (route_id) REFERENCES ROUTE(id)
			ON DELETE CASCADE;

ALTER TABLE TRIP	
	ADD CONSTRAINT FK_TRIP_COMPANY
		FOREIGN KEY (company_id) REFERENCES COMPANY(id)
			ON DELETE CASCADE;
		
CREATE TABLE TIME_TABLE (
	id int NOT NULL AUTO_INCREMENT,
	trip_id int NOT NULL,
	stop_number int NOT NULL,
	price int NOT NULL,
	arrival	datetime NOT NULL,
	departure datetime NOT NULL,
	vacant_seats tinyint NULL,
	CONSTRAINT U_TIME_TABLE_TRIP UNIQUE (trip_id, stop_number),
    CONSTRAINT PK_TIME_TABLE PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

ALTER TABLE TIME_TABLE
	ADD CONSTRAINT FK_TIME_TABLE_TRIP
		FOREIGN KEY (trip_id) REFERENCES TRIP(id)
			ON DELETE CASCADE;

CREATE TABLE PERSON_TRIP (
	id int NOT NULL AUTO_INCREMENT,
	person_id int NOT NULL,
	trip_id int NOT NULL,
	departure_stop_id int NOT NULL,
	arrival_stop_id int NOT NULL,
	CONSTRAINT U_PERSON_TRIP UNIQUE (person_id, trip_id),
    CONSTRAINT PK_PERSON_TRIP PRIMARY KEY (id ASC)
) DEFAULT CHARSET=utf8;

ALTER TABLE PERSON_TRIP	
	ADD CONSTRAINT FK_PERSON_TRIP_PERSON 
		FOREIGN KEY (person_id) REFERENCES PERSON(id)
			ON DELETE CASCADE;

ALTER TABLE PERSON_TRIP
	ADD CONSTRAINT FK_PERSON_TRIP_TRIP
		FOREIGN KEY (trip_id) REFERENCES TRIP(id)
			ON DELETE CASCADE;

ALTER TABLE PERSON_TRIP
	ADD CONSTRAINT FK_PERSON_TRIP_STOP_1
		FOREIGN KEY (departure_stop_id) REFERENCES STOP(id)
			ON DELETE CASCADE;
        
ALTER TABLE PERSON_TRIP
	ADD CONSTRAINT FK_PERSON_TRIP_STOP_2
		FOREIGN KEY (arrival_stop_id) REFERENCES STOP(id)
			ON DELETE CASCADE;

CREATE TRIGGER TR_TIME_TABLE BEFORE INSERT ON TIME_TABLE
	FOR EACH ROW
		SET NEW.vacant_seats = IFNULL((SELECT seats FROM TRIP WHERE NEW.trip_id = TRIP.id), 0);
	
CREATE TRIGGER TR_PERSON_TRIP_INS AFTER INSERT ON PERSON_TRIP
	FOR EACH ROW
		UPDATE TIME_TABLE SET vacant_seats = vacant_seats - 1
			WHERE stop_number >= (SELECT stop_number FROM ROUTE_STOPS 
								  	WHERE stop_id = NEW.departure_stop_id 
								  	AND route_id = (SELECT route_id FROM TRIP WHERE id = NEW.trip_id))
			AND
				stop_number < (SELECT stop_number FROM ROUTE_STOPS 
								  	WHERE stop_id = NEW.arrival_stop_id 
								  	AND route_id = (SELECT route_id FROM TRIP WHERE id = NEW.trip_id))
			AND trip_id = NEW.trip_id;
			
CREATE TRIGGER TR_PERSON_TRIP_DEL AFTER DELETE ON PERSON_TRIP
	FOR EACH ROW
		UPDATE TIME_TABLE SET vacant_seats = vacant_seats + 1
			WHERE stop_number >= (SELECT stop_number FROM ROUTE_STOPS 
								  	WHERE stop_id = OLD.departure_stop_id 
								  	AND route_id = (SELECT route_id FROM TRIP WHERE id = OLD.trip_id))
			AND
				stop_number < (SELECT stop_number FROM ROUTE_STOPS 
								  	WHERE stop_id = OLD.arrival_stop_id 
								  	AND route_id = (SELECT route_id FROM TRIP WHERE id = OLD.trip_id))
			AND trip_id = OLD.trip_id;
			
CREATE TRIGGER TR_PERSON_DEL BEFORE DELETE ON PERSON
	FOR EACH ROW
		DELETE FROM PERSON_TRIP
			WHERE person_id = OLD.id;